package com.seeyon.apps.brwh.abstraction;

import com.seeyon.apps.brwh.interfacese.ExcelInterface;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;

/**
 * @author chy
 * @version 1.0
 * @since 2020/2/26 excel工具类
 */
public abstract class ExcelUtil implements ExcelInterface {
	private final static Log log = LogFactory.getLog(ExcelUtil.class);

	/**
	 * key :Rid value ImagePath
	 */
	private static Map<String, String> imageMap = new HashMap<>();
	/**
	 * key imageId value Rid
	 */
	private static Map<String, String> imageIdMappingMap = new HashMap<>();


	/**
	 * 文件跟目录
	 */
	private String rootPath = "";

	/**
	 * @使用此方法必须重构ExcelInterFace中的wb方法 生成下载excel模板
	 * @param excelName
	 * @param response
	 * @throws Exception
	 */
	public void downLoadExcel(String excelName, HttpServletResponse response) throws Exception {
		response.setContentType("application/octet-stream;charset=ISO8859-1");
		response.setHeader("Content-Disposition",
				"attachment;filename=" + new String(excelName.getBytes(), "ISO8859-1"));
		response.addHeader("Pargam", "no-cache");
		response.addHeader("Cache-Control", "no-cache");
		response.setHeader("Access-Control-Allow-Origin", "*");
		response.setHeader("Access-Control-Allow-Methods", "POST, GET, DELETE, OPTIONS, DELETE");
		response.setHeader("Access-Control-Allow-Headers",
				"Content-Type, x-requested-with, X-Custom-Header, HaiYi-Access-Token");
		ServletOutputStream out = response.getOutputStream();
		try {
			HSSFWorkbook wb = wb();
			if (wb == null) {
				return;
			}
			wb.write(out);
			if (wb != null) {
				wb.close();
			}
		} catch (IOException e) {
			log.error("文件流操作异常" + e);
		} finally {
			if (out != null) {
				out.close();
			}
		}
	}

	/**
	 * 将excel文件解析为List<String[]>（支持后缀为.xls和.xlsx）
	 *
	 * @param inputStream
	 * @return
	 * @throws IOException
	 */
	public static Map<String,Object> excelToList(InputStream inputStream, String fileName) throws IOException {
		HashMap<String, Object> map = new HashMap<>();
		List<String[]> list = new ArrayList<>();
		Workbook wb = null;
		String substring = fileName.substring(fileName.lastIndexOf("."));
		if (".xls".equals(substring)) {
			wb = new HSSFWorkbook(inputStream);
		} else if (".xlsx".equals(substring)) {
			wb = new XSSFWorkbook(inputStream);
		}
		Sheet sheet = wb.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		Row row1 = sheet.getRow(0);
		int lastCellNum = row1.getLastCellNum();
		getList(sheet, list, lastRowNum, lastCellNum);
		//获取图片数据
		HashMap<String,HashMap<String, Object>> picInfo = getPicInfo(sheet);
		if (inputStream != null) {
			inputStream.close();
		}
		if (wb != null) {
			wb.close();
		}
		map.put("dataList", list);
		map.put("imgList", picInfo);
		return map;
	}


	/**
	 * 将excel文件解析为List<String[]>【一个excel中包含多个sheet的解析】
	 * 
	 * @param inputStream
	 * @param fileName
	 * @param collect     sheet名称
	 * @return
	 * @throws IOException
	 */
	public static ArrayList<HashMap> excelSheetsToList(InputStream inputStream, String fileName, List<String> collect)
			throws IOException {
		ArrayList<HashMap> hashMaps = new ArrayList<>();
		Workbook wb = null;
		String substring = fileName.substring(fileName.lastIndexOf("."));
		if (".xls".equals(substring)) {
			wb = new HSSFWorkbook(inputStream);
		} else if (".xlsx".equals(substring)) {
			wb = new XSSFWorkbook(inputStream);
		}
		int numberOfSheets = wb.getNumberOfSheets();
		for (int i = 0; i < numberOfSheets; i++) {
			HashMap<String, Object> hashMap = new HashMap<>();
			Sheet sheet = wb.getSheetAt(i);
			String sheetName = sheet.getSheetName();
			if (collect.contains(sheetName)) {
				List<String[]> list = new ArrayList<>();
				int lastRowNum = sheet.getLastRowNum();
				Row row1 = sheet.getRow(0);
				int lastCellNum = row1.getLastCellNum();

				getList(sheet, list, lastRowNum, lastCellNum);
				hashMap.put("sheetDateList", list);
				hashMap.put("status", 1);
				hashMaps.add(hashMap);
			} else {
				hashMap.put("sheetName", sheetName);
				hashMap.put("sheetDateList", "");
				hashMap.put("status", 0);
				hashMaps.add(hashMap);
			}
			hashMap.put("sheetName", sheetName);
			hashMap.put("page", i + 1);
		}
		return hashMaps;
	}


	/**
	 * 创建Excel表下拉数据
	 * @param sheet
	 * @param firstRowNum     首行
	 * @param firstCellNum    首列
 	 * @param lastRowNum      最后一行
	 * @param lastCellNum     最后一列
	 * @param dataList        下拉数据
	 */
	public static void addExcelXlData(HSSFSheet sheet,int firstRowNum, int firstCellNum, int lastRowNum, int lastCellNum,String[] dataList){
		//创建下拉列表区
		CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRowNum, lastRowNum, firstCellNum, lastCellNum);
		//创建下拉数据
		DVConstraint explicitListConstraint = DVConstraint.createExplicitListConstraint(dataList);
		HSSFDataValidation hssfDataValidation = new HSSFDataValidation(cellRangeAddressList, explicitListConstraint);
		sheet.addValidationData(hssfDataValidation);
	}



	/**
	 * 获取Excel表列数据
	 * 
	 * @param sheet
	 * @param list
	 * @param lastRowNum
	 * @param lastCellNum
	 */
	private static void getList(Sheet sheet, List<String[]> list, int lastRowNum, int lastCellNum) throws IOException {
		int flog;
		for (int i = 0; i <= lastRowNum; i++) {
			flog = 0;
			Row row = sheet.getRow(i);
			lastCellNum = row.getLastCellNum();
			String[] data = new String[lastCellNum];
			for (int j = 0; j < lastCellNum; j++) {
				if (row == null) {
					flog = 1;
					break;
				}
				Cell cell = row.getCell(j);
				if (j == 0 && cell == null) {
					flog = 1;
					break;
				}
				// Cell cell = row.getCell(j);
				if (cell == null) {
					data[j] = "";
				} else {
					String cellDataType = getCellDataType(cell);
					// 获取单元格数据
					Object cellData = getCellData(cell);
					if("FORMULA".equals(cellDataType)){
						if (cellData.toString().contains("DISPIMG")) {
							String imageId = subImageId(cellData.toString());
							String picPath = getImplantPicById(imageId);
//							InputStream picInputStream = openFile("xl/" + picPath);
//							File pic = saveFile(picInputStream, this.imageTargetPath + "/" + getFileRealName(picPath));
							data[j] = picPath;
						}
					}else{
						cell.setCellType(CellType.STRING);
						data[j] = cell.getStringCellValue();
					}
				}
			}
			if (flog == 0) {
				list.add(data);
			}
		}
	}


	/**
	 * 获取单元格数据
	 * @param cell
	 * @return
	 */
	private static Object getCellData(Cell cell) {
		String cellType = cell.getCellType().toString();
		Object value = null;
		switch (cellType) {
			case "STRING":
				value = cell.getStringCellValue();
				break;
			case "NUMERIC":
				if (DateUtil.isCellDateFormatted(cell))
				{
					value = DateUtil.getJavaDate(cell.getNumericCellValue()); // POI Excel 日期格式转换
				}else{
					BigDecimal number = BigDecimal.valueOf(cell.getNumericCellValue());
					value = number.toString();
				}
				break;
			case "BOOLEAN":
				value = cell.getBooleanCellValue();
				break;
			case "FORMULA":
				value = cell.getCellFormula();
				break;
			default:
				break;
		}
		return value;
	}





	/**
	 * 获取单元格类型
	 * @param cell
	 * @return
	 */
	private static String getCellDataType(Cell cell) {

		String cellType = cell.getCellType().toString();
		String type = null;
		switch (cellType) {
			case "STRING":
				type = "STRING";
				break;
			case "NUMERIC":
				type = "NUMERIC";
				break;
			case "BOOLEAN":
				type = "BOOLEAN";
				break;
			case "FORMULA":
				type = "FORMULA";
				break;
			default:
				type = "STRING";
		}
		return type;
	}

	/**
	 * 获取xls中的图片信息
	 * @param sheet
	 * @return
	 * @throws IOException
	 */
	private static HashMap<String,HashMap<String, Object>> getPicInfo(Sheet sheet){
		HashMap<String,HashMap<String, Object>> excelPicInfoList = new HashMap<>();
		// 获取图片信息
		HSSFSheet hssfSheet = (HSSFSheet) sheet;
		//获取所有图像形状
		HSSFPatriarch drawingPatriarch = hssfSheet.getDrawingPatriarch();
		if(drawingPatriarch != null){
			List<HSSFShape> shapes = hssfSheet.getDrawingPatriarch().getChildren();
			if(!shapes.isEmpty()){
				//遍历所有图像形状
				for (HSSFShape shape : shapes) {
					//获取形状在工作表中的顶点位置信息
					HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
					if (shape instanceof HSSFPicture) {
						HashMap<String, Object> map = new HashMap<>();
						//获取对应的图片数据
						HSSFPicture picture = (HSSFPicture) shape;
						HSSFPictureData pictureData = picture.getPictureData();
						//图片形状在工作表中的位置，所在行列起点和终点位置
						short col1 = anchor.getCol1();
						int row1 = anchor.getRow1();
						String imgwz = row1+"行"+col1+"列";
						byte[] bytes = pictureData.getData();
						//获取文件的扩展名
						String s = pictureData.suggestFileExtension();
						String fileName = picture.getFileName();
						map.put("imgData", bytes);
						map.put("imgRow",row1);
						map.put("imgCol",col1);
						map.put("imgHz",s);
						map.put("imgName",fileName);
						excelPicInfoList.put(imgwz,map);
					}
				}
			}
		}

		return excelPicInfoList;
	}





	/**
	 * @description [截取函数中图片的ID]
	 */
	private static String subImageId(String imageId) {
		return imageId.substring(imageId.indexOf("ID_") + 3, imageId.lastIndexOf("\""));
	}



	/**
	 * @description [使用嵌入式图片ID获取嵌入式图片地址]
	 */
	private static String getImplantPicById(String imageId) throws IOException {
		String imageRid = imageIdMappingMap.get(imageId);
		String imagePath = imageMap.get(imageRid);
		return imagePath;
	}


	private InputStream openFile(String filePath) {
		try {

			File file = new File(rootPath);
			ZipFile zipFile = new ZipFile(file);
			ZipInputStream zipInputStream = new ZipInputStream(new FileInputStream(file));

			ZipEntry nextEntry = null;
			while ((nextEntry = zipInputStream.getNextEntry()) != null) {
				String name = nextEntry.getName();
				if (name.equalsIgnoreCase(filePath)) {
					return zipFile.getInputStream(nextEntry);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

}
